package com.fhminyi.dao;

import com.fhminyi.model.Product;
import com.fhminyi.util.DBUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class ProductDao {
    // 获取所有商品
    public List<Product> getAllProducts() throws SQLException {
        List<Product> products = new ArrayList<>();
        String sql = "SELECT * FROM 商品";
        try (Connection conn = DBUtil.getConnection();
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {
            while (rs.next()) {
                Product product = new Product();
                product.setProductId(rs.getInt("商品_id"));
                product.setUserId(rs.getInt("用户_id"));
                product.setProductName(rs.getString("商品名称"));
                product.setDescription(rs.getString("描述"));
                product.setPrice(rs.getDouble("价格"));
                product.setStock(rs.getInt("库存"));
                product.setImageLocation(rs.getString("图片位置"));
                product.setCategory(rs.getString("分类"));
                product.setCreateTime(rs.getTimestamp("创建时间"));
                product.setUpdateTime(rs.getTimestamp("更新时间"));
                products.add(product);
            }
        }
        return products;
    }

    // 根据商品ID获取商品
    public Product getProductById(int productId) throws SQLException {
        String sql = "SELECT * FROM 商品 WHERE 商品_id = ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, productId);
            try (ResultSet rs = pstmt.executeQuery()) {
                if (rs.next()) {
                    Product product = new Product();
                    product.setProductId(rs.getInt("商品_id"));
                    product.setUserId(rs.getInt("用户_id"));
                    product.setProductName(rs.getString("商品名称"));
                    product.setDescription(rs.getString("描述"));
                    product.setPrice(rs.getDouble("价格"));
                    product.setStock(rs.getInt("库存"));
                    product.setImageLocation(rs.getString("图片位置"));
                    product.setCategory(rs.getString("分类"));
                    product.setCreateTime(rs.getTimestamp("创建时间"));
                    product.setUpdateTime(rs.getTimestamp("更新时间"));
                    return product;
                }
            }
        }
        return null;
    }

    // 添加根据用户id查找商品列表的方法
    public List<Product> getProductsByUserId(int userId) throws SQLException {
        List<Product> products = new ArrayList<>();
        String sql = "SELECT * FROM 商品 WHERE 用户_id = ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, userId);
            try (ResultSet rs = pstmt.executeQuery()) {
                while (rs.next()) {
                    Product product = new Product();
                    product.setProductId(rs.getInt("商品_id"));
                    product.setUserId(rs.getInt("用户_id"));
                    product.setProductName(rs.getString("商品名称"));
                    product.setDescription(rs.getString("描述"));
                    product.setPrice(rs.getDouble("价格"));
                    product.setStock(rs.getInt("库存"));
                    product.setImageLocation(rs.getString("图片位置"));
                    product.setCategory(rs.getString("分类"));
                    product.setCreateTime(rs.getTimestamp("创建时间"));
                    product.setUpdateTime(rs.getTimestamp("更新时间"));
                    products.add(product);
                }
            }
        }
        return products;
    }

    // 添加商品
    public void addProduct(Product product) throws SQLException {
        String sql = "INSERT INTO 商品 (用户_id, 商品名称, 描述, 价格, 库存, 图片位置, 分类) VALUES (?, ?, ?, ?, ?, ?, ?)";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, product.getUserId());
            pstmt.setString(2, product.getProductName());
            pstmt.setString(3, product.getDescription());
            pstmt.setDouble(4, product.getPrice());
            pstmt.setInt(5, product.getStock());
            pstmt.setString(6, product.getImageLocation());
            pstmt.setString(7, product.getCategory());
            pstmt.executeUpdate();
        }
    }

    // 更新商品
    public void updateProduct(Product product) throws SQLException {
        String sql = "UPDATE 商品 SET 用户_id = ?, 商品名称 = ?, 描述 = ?, 价格 = ?, 库存 = ?, 图片位置 = ?, 分类 = ? WHERE 商品_id = ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, product.getUserId());
            pstmt.setString(2, product.getProductName());
            pstmt.setString(3, product.getDescription());
            pstmt.setDouble(4, product.getPrice());
            pstmt.setInt(5, product.getStock());
            pstmt.setString(6, product.getImageLocation());
            pstmt.setString(7, product.getCategory());
            pstmt.setInt(8, product.getProductId());
            pstmt.executeUpdate();
        }
    }

    // 删除商品
    public void deleteProduct(int productId) throws SQLException {
        String sql = "DELETE FROM 商品 WHERE 商品_id = ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, productId);
            pstmt.executeUpdate();
        }
    }
}